﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Configuration;
using System.IO;
using System.Net;


namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        OleDbConnection conn;
        OleDbDataAdapter oda;
        DataSet ds;


        private void button1_Click(object sender, EventArgs e)
        {

            OleDbCommand cmd = new OleDbCommand("select * from TEST", conn);
            oda = new OleDbDataAdapter();
            oda.SelectCommand = cmd;
            ds = new DataSet();
            oda.Fill(ds, "test");
            dataGridView1.DataSource = ds.Tables[0];

        }


        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {

                textBox1.Text = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();
                textBox2.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
                textBox3.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
                textBox4.Text = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();

        }

        private void Form1_Load(object sender, EventArgs e)
        {

            string conString = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
            conn = new OleDbConnection(conString);

        }

        private void button2_Click(object sender, EventArgs e)
        {
            string id = textBox1.Text.ToString();
            string name = textBox2.Text;
            string sex = textBox3.Text;
            string age = textBox4.Text.ToString();
            string instring = "insert into test (id,name,sex,age) values ("+id+",'"+name+"','"+sex+"',"+age+")";
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(instring, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            oda.Fill(ds, "test");

        }

        private void button4_Click(object sender, EventArgs e)
        {
            string id = textBox1.Text.ToString();
            string destring = "delete from test where id ="+id;
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(destring,conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            oda.Fill(ds, "test");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string name = textBox2.Text;
            string sex = textBox3.Text;
            string age = textBox4.Text.ToString();
            string id = textBox1.Text.ToString();
            string instring = "update test set name ='"+name+"', sex ='"+sex+"', age ="+age+" where id ="+id;
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(instring, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            oda.Fill(ds, "test");
        }

        private void button5_Click(object sender, EventArgs e)
        {
            DataTable dt=new DataTable();
            oda.Fill(dt);
            datatableToCSV(dt,"d:\\test.csv");
            
        }

        public static bool datatableToCSV(DataTable dt, string pathFile)
        {
            string strLine = "";
            StreamWriter sw;
            try
            {
                sw = new StreamWriter(pathFile, false, System.Text.Encoding.GetEncoding(-0)); //覆盖
                //表头
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (i > 0)
                        strLine += ",";
                    strLine += dt.Columns[i].ColumnName;
                }
                strLine.Remove(strLine.Length - 1);
                sw.WriteLine(strLine);
                strLine = "";
                //表的内容
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    strLine = "";
                    int colCount = dt.Columns.Count;
                    for (int k = 0; k < colCount; k++)
                    {
                        if (k > 0 && k < colCount)
                            strLine += ",";
                        if (dt.Rows[j][k] == null)
                            strLine += "";
                        else
                        {
                            string cell = dt.Rows[j][k].ToString().Trim();
                            //防止里面含有特殊符号
                            cell = cell.Replace("\"", "\"\"");
                            cell = "\"" + cell + "\"";
                            strLine += cell;
                        }
                    }
                    sw.WriteLine(strLine);
                }
                sw.Close();
                string msg = "数据被成功导出到：" + pathFile;
                MessageBox.Show(msg);
            }
            catch (Exception ex)
            {
                string msg = "导出错误：" + pathFile;
                MessageBox.Show(msg);
                return false;
            }
            return true;
        }

        private void button6_Click(object sender, EventArgs e)
        {
            ftpfile(@"/zg/test.csv", @"d:\test.csv");
        }

        public void ftpfile(string ftpfilepath, string inputfilepath)
        {
            string ftphost = "172.16.18.77";
            //here correct hostname or IP of the ftp server to be given  

            string ftpfullpath = "ftp://" + ftphost + ftpfilepath;
            FtpWebRequest ftp = (FtpWebRequest)FtpWebRequest.Create(ftpfullpath);
            ftp.Credentials = new NetworkCredential("xxk", "xxk6626");
            //userid and password for the ftp server to given  

            ftp.KeepAlive = true;
            ftp.UseBinary = true;
            ftp.Method = WebRequestMethods.Ftp.UploadFile;
            FileStream fs = File.OpenRead(inputfilepath);
            byte[] buffer = new byte[fs.Length];
            fs.Read(buffer, 0, buffer.Length);
            fs.Close();
            Stream ftpstream = ftp.GetRequestStream();
            ftpstream.Write(buffer, 0, buffer.Length);
            ftpstream.Close();
            Log log = new Log(AppDomain.CurrentDomain.BaseDirectory + @"/log/Log.txt");
            log.log("上传成功！");
        }

        public class Log
        {
            private string logFile;
            private StreamWriter writer;
            private FileStream fileStream = null;

            public Log(string fileName)
            {
                logFile = fileName;
                CreateDirectory(logFile);
            }

            public void log(string info)
            {

                try
                {
                    System.IO.FileInfo fileInfo = new System.IO.FileInfo(logFile);
                    if (!fileInfo.Exists)
                    {
                        fileStream = fileInfo.Create();
                        writer = new StreamWriter(fileStream);
                    }
                    else
                    {
                        fileStream = fileInfo.Open(FileMode.Append, FileAccess.Write);
                        writer = new StreamWriter(fileStream);
                    }
                    writer.WriteLine(DateTime.Now + ": " + info);

                }
                finally
                {
                    if (writer != null)
                    {
                        writer.Close();
                        writer.Dispose();
                        fileStream.Close();
                        fileStream.Dispose();
                    }
                }
            }

            public void CreateDirectory(string infoPath)
            {
                DirectoryInfo directoryInfo = Directory.GetParent(infoPath);
                if (!directoryInfo.Exists)
                {
                    directoryInfo.Create();
                }
            }
        } 

    }
}
